Amazon AuroraのヘッドレスDBクラスターを作成してみた

$ get_secrets_value=$(aws secretsmanager get-secret-value \
    --secret-id AuroraSecret7ACECA7F-BLZxpBZDcJDq \
    --region us-east-1 \
    | jq -r .SecretString)

$ export PGHOST=$(echo "${get_secrets_value}" | jq -r .host)
$ export PGPORT=$(echo "${get_secrets_value}" | jq -r .port)
$ export PGDATABASE=$(echo "${get_secrets_value}" | jq -r .dbname)
$ export PGUSER=$(echo "${get_secrets_value}" | jq -r .username)
$ export PGPASSWORD=$(echo "${get_secrets_value}" | jq -r .password)

$ curl https://ftp.postgresql.org/pub/projects/pgFoundry/dbsamples/iso-3166/iso-3166-1.0/iso-3166-1.0.tar.gz \
  -s \
  -o iso-3166-1.0.tar.gz

$ tar zxvf iso-3166-1.0.tar.gz
iso-3166/
iso-3166/iso-3166.sql

$ createdb iso

$ psql -f iso-3166/iso-3166.sql iso
BEGIN
SET
CREATE TABLE
COPY 242
CREATE TABLE
COPY 3995
COMMIT
ANALYZE
ANALYZE

$ psql iso
psql (15.6, server 14.10)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.

iso=> \dt
           List of relations
 Schema |    Name    | Type  |  Owner
--------+------------+-------+----------
 public | country    | table | postgres
 public | subcountry | table | postgres
(2 rows)

iso=> SELECT count(*) FROM country;
 count
-------
   242
(1 row)

iso=> SELECT count(*) FROM subcountry;
 count
-------
  3995
(1 row)

    this.dbCluster.node.children.forEach((child) => {
      if (child.node.defaultChild instanceof cdk.aws_rds.CfnDBInstance) {
        this.dbCluster.node.tryRemoveChild(child.node.id);
      }
    });

$ npx cdk diff
Stack AuroraStack
Hold on while we create a read-only change set to get a diff with accurate replacement information (use --no-change-set to use a less accurate but faster template-only diff)
Resources
[-] AWS::RDS::DBInstance Aurora/Default/Writer AuroraWriter14FF9353 destroy

✨  Number of stacks with differences: 1

$ psql iso
psql: error: could not translate host name "db-cluster.cluster-cicjym7lykmq.us-east-1.rds.amazonaws.com" to address: Name or service not known

$ dig db-cluster.cluster-cicjym7lykmq.us-east-1.rds.amazonaws.com

; <<>> DiG 9.16.48-RH <<>> db-cluster.cluster-cicjym7lykmq.us-east-1.rds.amazonaws.com
;; global options: +cmd
;; Got answer:
;; ->>HEADER<<- opcode: QUERY, status: NXDOMAIN, id: 2256
;; flags: qr rd ra; QUERY: 1, ANSWER: 1, AUTHORITY: 1, ADDITIONAL: 1

;; OPT PSEUDOSECTION:
; EDNS: version: 0, flags:; udp: 4096
;; QUESTION SECTION:
;db-cluster.cluster-cicjym7lykmq.us-east-1.rds.amazonaws.com. IN        A

;; ANSWER SECTION:
db-cluster.cluster-cicjym7lykmq.us-east-1.rds.amazonaws.com. 5 IN CNAME db-instance-writer.cicjym7lykmq.us-east-1.rds.amazonaws.com.

;; AUTHORITY SECTION:
us-east-1.rds.amazonaws.com. 10 IN      SOA     ns-1420.awsdns-49.org. awsdns-hostmaster.amazon.com. 1 7200 900 1209600 86400

;; Query time: 0 msec
;; SERVER: 10.1.1.2#53(10.1.1.2)
;; WHEN: Sun Mar 24 08:31:31 UTC 2024
;; MSG SIZE  rcvd: 216

$ aws rds-data execute-statement \
  --resource-arn arn:aws:rds:us-east-1:<AWSアカウントID>:cluster:db-cluster \
  --secret-arn arn:aws:secretsmanager:us-east-1:<AWSアカウントID>:secret:AuroraSecret7ACECA7F-BLZxpBZDcJDq-sGEKy7 \
  --database iso \
  --sql 'SELECT count(*) FROM country;'

An error occurred (DatabaseNotFoundException) when calling the ExecuteStatement operation: Cannot find DBInstance in DBCluster arn:aws:rds:us-east-1:<AWSアカウントID>:cluster:db-cluster.

$  npx cdk diff
Stack AuroraStack
Hold on while we create a read-only change set to get a diff with accurate replacement information (use --no-change-set to use a less accurate but faster template-only diff)
Resources
[+] AWS::RDS::DBInstance Aurora/Default/Writer AuroraWriter14FF9353 

✨  Number of stacks with differences: 1

$ psql iso
psql (15.6, server 14.10)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.

iso=> \dt
           List of relations
 Schema |    Name    | Type  |  Owner
--------+------------+-------+----------
 public | country    | table | postgres
 public | subcountry | table | postgres
(2 rows)

iso=> SELECT count(*) FROM country;
 count
-------
   242
(1 row)

iso=> SELECT count(*) FROM subcountry;
 count
-------
  3995
(1 row)

iso=> SELECT * FROM country LIMIT 10;
        name         | two_letter | country_id
---------------------+------------+------------
 Afghanistan         | AF         |          4
 Albania             | AL         |          8
 Algeria             | DZ         |         12
 American Samoa      | AS         |         16
 Andorra             | AD         |         20
 Angola              | AO         |         24
 Anguilla            | AI         |        660
 Antarctica          | AQ         |         10
 Antigua and Barbuda | AG         |         28
 Argentina           | AR         |         32
(10 rows)

iso=> SELECT * FROM subcountry LIMIT 10;
 country | subcountry_name |      subdivision      | subcountry_level
---------+-----------------+-----------------------+------------------
 AD      | AN              | Andorra la Vella      |
 AD      | CA              | Canillo               |
 AD      | EE              | Escaldes-Engordany    |
 AD      | EN              | Encamp                |
 AD      | JL              | Sant Julià de Lòria   |
 AD      | MA              | La Massana            |
 AD      | OR              | Ordino                |
 AE      | AJ              | Ajman                 |
 AE      | AZ              | Abu Z¸aby [Abu Dhabi] |
 AE      | DU              | Dubayy [Dubai]        |
(10 rows)